# coding: utf-8
from __future__ import unicode_literals
import psutil
import time
import MySQLdb
import commands
import pymysql
from threading import Lock, Event
import paramiko
conn = pymysql.connect(host="10.33.64.99", user="root", passwd="vdin1234", db="tsgz", charset="utf8")
cursor = conn.cursor()
# cursor.execute("SELECT VERSION()")
# data = cursor.fetchone()
# print "Database version : %s " % data
# conn.close()


class SysSSHClient(object):
    def __init__(self, hostname, username, password, port=22, keepalive=None):
        self.hostname = hostname
        self.username = username
        self.password = password
        self.port = port
        self.keepalive = keepalive
        self.connected = Event()
        self.lock = Lock()
        self.ssh = paramiko.SSHClient()
        self.ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())

    def _do_connect(self):
        self.ssh.connect(hostname=self.hostname, username=self.username, password=self.password, port=self.port)

    def _connect(self):
        """ Connect to the remote if not already connected. """
        if not self.connected.is_set():
            try:
                self.lock.acquire()
                # Another thread may have connected while we were
                # waiting to acquire the lock
                if not self.connected.is_set():
                    self._do_connect()
                    self.connected.set()
            except :
                raise
            finally:
                self.lock.release()

    def run_command(self, command):
        self._connect()
        stdin, stdout, stderr = self.ssh.exec_command(command)
        return stdout

    def close(self):
        self.ssh.close()

ssh = SysSSHClient(hostname='10.33.64.99', username='root', password='Vdin1234', port=22)


# def read_current_flow(password='Vdin1234', ip='1.1.1.1', oid='ifInOctets.777'):
#     cmd = 'snmpwalk -v 2c -c %s %s %s' % (password, ip, oid)
#     status, result = commands.getstatusoutput(cmd)
#     if status != 0:
#         return "Error, %s" % result
#     return result
time_list = [ str(i)+':00' if i>=10 else '0'+str(i)+':00' for i in range(24)]
oids = ['ifOutOctets.777', 'ifInOctets.777', 'ifOutOctets.1025', 'ifInOctets.1025']
inits = []
for oid in oids:
    cmd = 'snmpwalk -v 2c -c Vdin1234 1.1.1.1 %s' % oid
    inits.append(int(ssh.run_command(cmd).read().strip().split()[-1]))

while True:
    time_str = time.strftime("%H%M")
    results = []
    for oid in oids:
        cmd = 'snmpwalk -v 2c -c Vdin1234 1.1.1.1 %s' % oid
        result = ssh.run_command(cmd).read().strip().split()[-1]
        # result = int(read_current_flow(oid=oid).split()[-1])
        results.append(result)
    print results
    try:
        cursor.execute("INSERT INTO app_totalnetflow(firewall_tx_total, firewall_rx_total, "
                       "switch_tx_total, switch_rx_total, recoder, add_time) VALUES (%s, %s, %s, %s, '定时更新', "
                       "%s)", (results[0], results[1], results[2], results[3], time.strftime("%Y-%m-%d %H:%M:%S")))
        conn.commit()
        print("提交成功. %s", time.time())
    except Exception as e:
        print e
    # if time_str in  time_list:
    #     if not cursor.execute("SELECT * FROM app_timeslotnetflow WHERE add_time LIKE '%{}%'".format(time_str)):
    #         try:
    #             cursor.execute("INSERT INTO app_timeslotnetflow(firewall_tx, firewall_rx, switch_tx, "
    #                            "switch_rx, start_time, end_time) VALUES (%s, %s, %s, %s, '定时更新', %s)",
    #                            (results[0], results[1], results[2], results[3], time.strftime("%Y-%m-%d %H:%M:%S")))
    #         except Exception as e:
    #             print e
    time.sleep(10)




